package tools;

import com.sun.deploy.util.StringUtils;
import io.netty.util.internal.StringUtil;
import pond.common.JSON;
import pond.db.DB;

import java.util.*;

/**
 * Created by sky_wang on 2017/2/7.
 */
public class CreateViewTool {
    private static DB db;
    public CreateViewTool(DB db) {
        this.db = db;
    }


    private static List<String> getListofDataTableName()
    {
        Map<String, Map<String, Integer>> mydb = db.getDbStructures();
        List<String> tmp= new ArrayList<>();
        System.out.println("data table list:");
        for(Map.Entry<String, Map<String, Integer>> entry : mydb.entrySet()) {
            String tableName = entry.getKey();
            if (tableName.startsWith("t_")) {
                tmp.add(tableName);
                System.out.println(tableName);
            }
        }
        return tmp;
    }

    private static boolean dataTableIsExisted(String tableName){
        List<String> list = getListofDataTableName();
        for (String item : list)
        {
            if (item.equals(tableName)) {
                return true;
            }
        }
        return false;
    }

    private static String getDataTableName(String keyName)
    {
        int loc = keyName.indexOf("_id");//首先获取字符的位置
        String dataTableName = keyName.substring(0, loc);//再对字符串进行截取，获得想要得到的字符串
        dataTableName = ("t_" +dataTableName);
        if (dataTableIsExisted(dataTableName))
        {
            return dataTableName;
        }
        else
        {
            return null;
        }
    }

    private static Map<String, Map<String, String>> getMapOfRelationshipTable()
    {
        Map<String, Map<String, Integer>> mydb = db.getDbStructures();
        Map<String, Map<String, String>> mapRelationshipTable = new HashMap<>();
        System.out.println("rel table map:");
        for(Map.Entry<String, Map<String, Integer>> entry : mydb.entrySet()) {
            String tableName = entry.getKey();
            if (tableName.startsWith("r_")) {
                System.out.println("key:"+tableName);
                Map<String, Integer> table = entry.getValue();
                Map<String, String> mapDataTable = new HashMap<>();
                for (Map.Entry<String, Integer> subentry : table.entrySet()) {
                    if (subentry.getKey().indexOf("_id") >= 0)
                    {
                        mapDataTable.put(getDataTableName(subentry.getKey()), subentry.getKey());
                    }
                    System.out.println("map:"+ getDataTableName(subentry.getKey())+ ";"+subentry.getKey());
                }
                mapRelationshipTable.put(tableName, mapDataTable);
            }
        }
        return mapRelationshipTable;
    }
    private static String geSqlJoinFromRelationshipTable(String sql, String mainTable, String subTable)
    {
        Map<String, Map<String, String>> mapRelationshipTable = getMapOfRelationshipTable();
        String sqlJoin ="";
        if ("".equals(sql)) {
            sql = mainTable;
        }
        for(Map.Entry<String, Map<String, String>> entry : mapRelationshipTable.entrySet()) {
            String relationshipTableName = entry.getKey();
            boolean mainTableIsExist = false;
            boolean subTableIsExist = false;
            String mainTableId = "";
            String subTableId = "";

            for (Map.Entry<String, String> subentry : entry.getValue().entrySet()) {
                if (mainTable.equals(subentry.getKey())){
                    mainTableIsExist = true;
                    mainTableId = subentry.getValue();
                }
                if (subTable.equals(subentry.getKey()))
                {
                    subTableIsExist = true;
                    subTableId = subentry.getValue();
                }
            }
            if (mainTableIsExist && subTableIsExist)
            {
                sqlJoin += "(" +sql+")" +" left join " + relationshipTableName + " on " +mainTable+"."+"id" + "="+ relationshipTableName +"."+ mainTableId;
                System.out.println("|||---->:\r\n"+sqlJoin);
                sqlJoin = "(" +sqlJoin+")" +" left join " + subTable + " on " +relationshipTableName+"."+subTableId + "="+ subTable +"."+ "id";
                System.out.println("|||2---->:\r\n" + sqlJoin);
            }
        }
        System.out.println("|||3---->:\r\n"+sqlJoin);
        return sqlJoin;
    }
    public String getCreateViewSql(String json)
    {
        String sql = "";
        String whereStr = " where 1= 1";
        Map<String,Object> viewMap = new LinkedHashMap<>();
        viewMap = JSON.parse(json);
        List<Map<String,String>> listColumns = new ArrayList<>();
        List<Map<String,Object>> listQueryOptions = new ArrayList<>();

        String mainTableName = null;
        String viewName = null;
        for (Map.Entry<String, Object> subentry : viewMap.entrySet()) {
            System.out.println("viewMap key:"+ subentry.getKey());
            if ("main_table".equals(subentry.getKey()))
            {
                mainTableName = (String)(subentry.getValue());
            }
            else if ("view_name".equals(subentry.getKey())){
                viewName = (String)(subentry.getValue());
            }
            else if ("columns".equals(subentry.getKey())){
                listColumns = (List<Map<String,String>>)(subentry.getValue());
            }
            else if ("query_options_ids".equals(subentry.getKey())){
                listQueryOptions = (List<Map<String,Object>>)(subentry.getValue());
            }
            else {
                continue;
            }
        }

        List <String> listDataTable = new ArrayList<>();
        List <String> strColumnsSql = new ArrayList<>();
        for (Map<String,String> item : listColumns)
        {
            System.out.println("map data:" + item.get("key")+ ":"+ item.get("value"));
            if (false == listDataTable.contains(item.get("value"))) {
                listDataTable.add(item.get("value"));
            }
            strColumnsSql.add(item.get("value")+"."+ item.get("key") + " as " + item.get("value")+"___"+item.get("key"));
        }
        String p = StringUtils.join(strColumnsSql, ",");
        System.out.println("p data:" +p);
        sql = "CREATE VIEW "+ viewName + " AS SELECT " + StringUtils.join(strColumnsSql,",") + " from ";
        String joinsql = "";
        for (String item : listDataTable)
        {
            System.out.println("data table:" + item);
            if (mainTableName.equals(item))
            {
                continue;
            }
            else
            {
                joinsql += geSqlJoinFromRelationshipTable(joinsql,mainTableName,item);
                System.out.println("join data:" +joinsql);
            }
        }

//        {"key":"t_user___name",
//                "value":{"key":"in","value":["wangfeng","gufei"]}},
        for (Map<String,Object> item : listQueryOptions)
        {
            String queryOptItem = (String)item.get("key");
            Map<String,Object> subQueryMap = (Map<String,Object>)item.get("value");
            String queryKey = (String)subQueryMap.get("key");
            if ("in".equals(queryKey))
            {
                List<Object>  subQueryValuesList = (List<Object> )subQueryMap.get("value");
                whereStr += " and " + queryOptItem.toString().replace("___",".") + " in " + subQueryValuesList.toString().replace("[", "(").replace(", ", "', '").replace("]", ")");;
            }
        }

        return sql +joinsql + whereStr +";";
    }

//    public String getValueByKeyFromJson(String key, String json)
//    {
//        Map<String,Object> viewMap = new LinkedHashMap<>();
//        viewMap = JSON.parse(json);
//        List<Map<String,String>> listColumns = new ArrayList<>();
//        for (Map.Entry<String, Object> subentry : viewMap.entrySet()) {
//            System.out.println(key + subentry.getKey());
//            if (key.equals(subentry.getKey())) {
//                return (String) (subentry.getValue());
//            }
//            else {
//                continue;
//            }
//        }
//        return null;
//    }

    public String getValueByKeyFromJson(String key, String json)
    {
        Map<String,Object> viewMap = new LinkedHashMap<>();
        viewMap = JSON.parse(json);
        List<Map<String,String>> listColumns = new ArrayList<>();
        for (Map.Entry<String, Object> subentry : viewMap.entrySet()) {
            System.out.println(key + subentry.getKey());
            if (key.equals(subentry.getKey())) {
                return subentry.getValue().toString();
            }
            else {
                continue;
            }
        }
        return null;
    }
    /* {"t_role":{"role_level":4,"role_name":12},"t_user":{"name":12,"num":12,"sex":12}} */
//    public String getJsonOfDataTable()
//    {
//        String json = null;
//        Map<String, Map<String, Integer>> mydb = db.getDbStructures();
//        Map<String, Map<String, Integer>> tmp= new HashMap<>();
//        for(Map.Entry<String, Map<String, Integer>> entry : mydb.entrySet()) {
//            String tableName = entry.getKey();
//            System.out.println(tableName);
//            if (tableName.startsWith("t_")) {
//                Map<String, Integer> table = entry.getValue();
//
//                for (Map.Entry<String, Integer> subentry : table.entrySet()) {
//                    if ("id".equals(subentry.getKey()))
//                    {
//                        table.remove(subentry.getKey());
//                    }
//                    System.out.println(subentry.getKey());
//
//                }
//                tmp.put(tableName, table);
//            }
//        }
//        json = JSON.stringify(tmp);
//        System.out.println("----\r\n"+ json);
//        return json;
//    }

/*
[{"name":"t_role","value":[{"name": "role_level", "value":4 },{"name": "role_name", "value":12}]},
{"name":"t_user","value":[{"name":"name", "value" : 12},{"name":"sex", "value" : 12}]}]
*/
    public String getJsonOfDataTable()
    {
        String json = null;
        Map<String, Map<String, Integer>> mydb = db.getDbStructures();
        Map<String, Map<String, Integer>> tmp= new HashMap<>();

        List<Map<String,Object>> listDataTable = new ArrayList<>();
        for(Map.Entry<String, Map<String, Integer>> entry : mydb.entrySet()) {
            String tableName = entry.getKey();
            Map<String,Object> mapTableItem  = new HashMap<>();


            System.out.println(tableName);
            if (tableName.startsWith("t_")) {
                Map<String, Integer> table = entry.getValue();
                List <Map<String,Object>> listColsOfTable = new ArrayList<>();
                for (Map.Entry<String, Integer> subentry : table.entrySet()) {
                    Map<String,Object> mapSubItem = new HashMap<>();
                    if ("id".equals(subentry.getKey()))
                    {
                        //table.remove(subentry.getKey());
                        continue;
                    }
                    mapSubItem.put("name", subentry.getKey());
                    mapSubItem.put("value",subentry.getValue());
                    listColsOfTable.add(mapSubItem);
                    System.out.println(subentry.getKey());
                }

                mapTableItem.put("name", tableName);
                mapTableItem.put("value",listColsOfTable);
                listDataTable.add(mapTableItem);
            }
        }
        json = JSON.stringify(listDataTable);
        System.out.println("----\r\n"+ json);
        return json;
    }
}
